﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;
using Newtonsoft.Json;
using System.Runtime.Serialization;
namespace GZ.Entity
{
    /// <summary>
    /// 产品实体类
    /// </summary>
    public class EProductInfo
    {
        public EProductInfo()
        {
            this.price = 0;

            this.productdescription = string.Empty;
            this.productename = string.Empty;
            this.productname = string.Empty;
            this.productstyle = string.Empty;
            this.categoryid = 0;
            this.isshelves = -1;
            this.ShoppingCartThumbUrl = string.Empty;
            this.taste = new List<Etaste>();
        }


        #region 属性
        private int id;

        public int Id
        {
            get { return id; }
            set { id = value; }
        }
        private string productname;

        public string ProductName
        {
            get { return productname; }
            set { productname = value; }
        }
        private string productename;

        public string ProductEName
        {
            get { return productename; }
            set { productename = value; }
        }
        private string productstyle;

        public string ProductStyle
        {
            get { return productstyle; }
            set { productstyle = value; }
        }
        private decimal price;

        public decimal Price
        {
            get { return price; }
            set { price = value; }
        }

        private string productdescription;
        [JsonIgnore]
        public string ProductDescription
        {
            get { return productdescription; }
            set { productdescription = value; }
        }

        private int categoryid;

        public int CategoryId
        {
            get { return categoryid; }
            set { categoryid = value; }
        }

        private int isshelves;

        public int IsShelves
        {
            get { return isshelves; }
            set { isshelves = value; }
        }

        private string shoppingcartthumburl;

        /// <summary>
        /// 购物车展示所属商品缩略图
        /// </summary>
        public string ShoppingCartThumbUrl
        {
            get { return shoppingcartthumburl; }
            set { shoppingcartthumburl = value; }
        }

        private string imgurl1;

        public string ImgUrl1
        {
            get { return imgurl1; }
            set { imgurl1 = value; }
        }

        private string imgurl2;
        [JsonIgnore]
        public string ImgUrl2
        {
            get { return imgurl2; }
            set { imgurl2 = value; }
        }

        private string imgurl3;
        [JsonIgnore]
        public string ImgUrl3
        {
            get { return imgurl3; }
            set { imgurl3 = value; }
        }

        private string imgurl4;
        [JsonIgnore]
        public string ImgUrl4
        {
            get { return imgurl4; }
            set { imgurl4 = value; }
        }

        private string imgurl5;
        [JsonIgnore]
        public string ImgUrl5
        {
            get { return imgurl5; }
            set { imgurl5 = value; }
        }


        private int orderid;

        public int OrderId
        {
            get { return orderid; }
            set { orderid = value; }
        }


        private IList<Etaste> taste;

        [JsonIgnore]
        public IList<Etaste> Taste
        {
            get
            {
                return taste;
            }
            set { taste = value; }
        }

        #endregion


        public override string ToString()
        {
            return JsonConvert.SerializeObject(this);
        }
        #region

        /// <summary>
        /// 根据分类名查找商品
        /// </summary>
        /// <param name="CategroyId">分类Id</param>
        /// <returns>商品集合</returns>
        public IList<EProductInfo> GetProductsByCategory(int CategroyId)
        {
            string sql = "SELECT Id,ImgUrl1 FROM tb_productinfo WHERE CategoryId=@CategoryId AND IsShelves=1 AND IsDelete=0 ORDER BY OrderId";
            MySqlParameter[] parameters = new MySqlParameter[1];
            parameters[0] = new MySqlParameter("@CategoryId", CategroyId);
            DataTable dt = MySqlHelper.ExecuteDataset(GZ.DAL.MySQLHelper.connStr, sql, parameters).Tables[0];
            IList<EProductInfo> list = GZ.Common.ModelConvertHelper<EProductInfo>.ConvertToModel(dt);
            return list;
        }

        /// <summary>
        /// 插入产品并返回Id
        /// </summary>
        /// <param name="obj">产品对象</param>
        /// <returns>新插入的产品Id，0为失败</returns>
        public int Insert(EProductInfo obj)
        {
            string sql = @"INSERT INTO tb_productinfo
                            (ProductName,
                            ProductEName,
                            ProductStyle,
                            Price,
                            ProductDescription,
                            CategoryId,
                            IsShelves, 
                            OrderId)
                            VALUES
                            (@ProductName,
                            @ProductEName,
                            @ProductStyle,
                            @Price,
                            @ProductDescription,
                            @CategoryId,
                            @IsShelves, 
                            @OrderId
                            );";
            MySqlParameter[] parameters = new MySqlParameter[8];
            parameters[0] = new MySqlParameter("@ProductName", obj.ProductName);
            parameters[1] = new MySqlParameter("@ProductEName", obj.ProductEName);
            parameters[2] = new MySqlParameter("@ProductStyle", obj.ProductStyle);
            parameters[3] = new MySqlParameter("@Price", obj.Price);
            parameters[4] = new MySqlParameter("@ProductDescription", obj.ProductDescription);
            parameters[5] = new MySqlParameter("@CategoryId", obj.CategoryId);
            parameters[6] = new MySqlParameter("@IsShelves", obj.IsShelves);
            parameters[7] = new MySqlParameter("@OrderId", obj.OrderId);

            MySqlHelper.ExecuteNonQuery(GZ.DAL.MySQLHelper.connStr, sql, parameters);
            sql = "SELECT Id FROM tb_productinfo WHERE ProductName=@ProductName AND IsDelete=0";
            parameters = new MySqlParameter[1];
            parameters[0] = new MySqlParameter("@ProductName", obj.ProductName);
            DataTable dt = MySqlHelper.ExecuteDataset(GZ.DAL.MySQLHelper.connStr, sql, parameters).Tables[0];
            if (dt.Rows.Count > 0)
            {
                return Convert.ToInt32(dt.Rows[0]["Id"]);
            }
            else
            {
                return obj.Id;
            }
        }

        /// <summary>
        /// 根据条件查询商品列表
        /// </summary>
        /// <param name="obj">条件对象</param>
        /// <returns>商品列表</returns>
        public DataTable GetProductListByCondition(EProductInfo obj)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"SELECT  Id ,
                                 ProductName ,
                                 ProductEName ,
                                 ProductStyle ,
                                 Price ,
                                 ProductDescription ,
                                 IsDelete ,  
                                 IsShelves ,
                                 ImgUrl1 ,
                                 ImgUrl2 ,
                                 ImgUrl3 ,
                                 ImgUrl4 ,
                                 ImgUrl5 ,
                                 OrderId ,
                                 ShoppingCartThumbUrl ,
                                 (SELECT CategoryName FROM tb_category WHERE Id=CategoryId AND IsDelete=0) AS                                   CategoryId FROM tb_productinfo WHERE 1=1 AND IsDelete=0 ");
            if (obj != null)
            {
                if (obj.Id > 0)
                {
                    sql.Append(" AND Id=" + obj.Id);
                }

                if (obj.ProductName.Length > 0)
                {
                    sql.Append(" AND ProductName='" + obj.ProductName + "'");
                }
                if (obj.Price > 0)
                {
                    sql.Append(" AND Price=" + obj.Price);
                }
                if (obj.ProductStyle.Length > 0)
                {
                    sql.Append(" AND ProductStyle='" + obj.ProductStyle + "'");
                }
                if (obj.CategoryId > 0)
                {
                    sql.Append(" AND CategoryId=" + obj.CategoryId);
                }
                if (obj.IsShelves != -1)
                {
                    sql.Append(" AND IsShelves=" + obj.IsShelves);
                }
            }
            sql.Append(" ORDER BY OrderId ");
            return MySqlHelper.ExecuteDataset(GZ.DAL.MySQLHelper.connStr, sql.ToString()).Tables[0];
        }

        /// <summary>
        /// 得到所有商品
        /// </summary>
        /// <returns></returns>
        public DataTable GetProducts()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"SELECT  Id ,
                                 ProductName ,
                                 ProductEName ,
                                 ProductStyle ,
                                 Price ,
                                 ProductDescription ,
                                 IsDelete ,  
                                 IsShelves ,
                                 ImgUrl1 ,
                                 ImgUrl2 ,
                                 ImgUrl3 ,
                                 ImgUrl4 ,
                                 ImgUrl5 ,
                                 OrderId ,
                                 ShoppingCartThumbUrl ,
                                 (SELECT CategoryName FROM tb_category WHERE Id=CategoryId AND IsDelete=0) AS                                   CategoryId FROM tb_productinfo WHERE 1=1 AND IsDelete=0 ");
            return MySqlHelper.ExecuteDataset(GZ.DAL.MySQLHelper.connStr, sql.ToString()).Tables[0];
        }

        /// <summary>
        /// 商品上架
        /// </summary>
        /// <param name="ProductId">商品Id</param>
        /// <returns>True为成功，False为失败</returns>
        public bool UpShelves(int ProductId)
        {
            string sql = "UPDATE tb_productinfo SET IsShelves=1 WHERE Id=@Id";
            MySqlParameter[] parameters = new MySqlParameter[1];
            parameters[0] = new MySqlParameter("@Id", ProductId);
            int val = MySqlHelper.ExecuteNonQuery(GZ.DAL.MySQLHelper.connStr, sql, parameters);
            return val > 0 ? true : false;
        }

        /// <summary>
        /// 商品下架
        /// </summary>
        /// <param name="ProductId">商品Id</param>
        /// <returns>True为成功，False为失败</returns>
        public bool DownShelves(int ProductId)
        {
            string sql = "UPDATE tb_productinfo SET IsShelves=0 WHERE Id=@Id";
            MySqlParameter[] parameters = new MySqlParameter[1];
            parameters[0] = new MySqlParameter("@Id", ProductId);
            int val = MySqlHelper.ExecuteNonQuery(GZ.DAL.MySQLHelper.connStr, sql, parameters);
            return val > 0 ? true : false;
        }

        /// <summary>
        /// 删除商品
        /// </summary>
        /// <param name="ProductId">商品ID</param>
        /// <returns>True为成功，False为失败</returns>
        public bool Del(int ProductId)
        {
            string sql = "UPDATE tb_productinfo SET IsDelete=1 WHERE Id=@Id";
            MySqlParameter[] parameters = new MySqlParameter[1];
            parameters[0] = new MySqlParameter("@Id", ProductId);
            int val = MySqlHelper.ExecuteNonQuery(GZ.DAL.MySQLHelper.connStr, sql, parameters);
            return val > 0 ? true : false;
        }

        /// <summary>
        /// 根据商品Id得到缩略图路径
        /// </summary>
        /// <param name="Id">商品Id</param>
        /// <returns>缩略图路径</returns>
        public string GetProductThumbnails(int Id)
        {
            string sql = @"SELECT ShoppingCartThumbUrl FROM tb_productinfo WHERE Id=@Id AND IsDelete=0";
            MySqlParameter[] parameters = new MySqlParameter[1];
            parameters[0] = new MySqlParameter("@Id", id);
            return MySqlHelper.ExecuteScalar(GZ.DAL.MySQLHelper.connStr, sql, parameters).ToString();
        }

        /// <summary>
        /// 获取指定商品信息
        /// </summary>
        /// <param name="Id">产品id</param>
        /// <returns>EProductInfo</returns>
        public EProductInfo GetProductInfoById(int Id)
        {
            MySqlParameter[] parameters = new MySqlParameter[1];
            parameters[0] = new MySqlParameter("@Id", Id);
            EProductInfo product = null;

            using (MySqlDataReader sdr = MySqlHelper.ExecuteReader(GZ.DAL.MySQLHelper.connStr, "select * from tb_productinfo where IsDelete=0 and Id=@Id", parameters))
            {
                if (sdr.Read())
                {
                    product = new EProductInfo();
                    product.ProductName = sdr["ProductName"].ToString();
                    product.Price = Convert.ToDecimal(sdr["Price"]);
                    product.CategoryId = Convert.ToInt32(sdr["CategoryId"]);
                    product.Id = Convert.ToInt32(sdr["Id"]);
                    product.IsShelves = Convert.ToInt32(sdr["IsShelves"]);
                    product.ProductDescription = sdr["ProductDescription"].ToString();
                    product.ProductEName = sdr["ProductEName"].ToString();
                    product.ProductStyle = sdr["ProductStyle"].ToString();
                    product.IsShelves = Convert.ToInt32(sdr["IsShelves"]);
                    product.ImgUrl1 = sdr["ImgUrl1"].ToString();
                    product.ImgUrl2 = sdr["ImgUrl2"].ToString();
                    product.ImgUrl3 = sdr["ImgUrl3"].ToString();
                    product.ImgUrl4 = sdr["ImgUrl4"].ToString();
                    product.ImgUrl5 = sdr["ImgUrl5"].ToString();
                    product.ShoppingCartThumbUrl = sdr["ShoppingCartThumbUrl"].ToString();
                    product.OrderId = Convert.ToInt32(sdr["OrderId"].ToString());
                }
            }
            return product;
        }

        /// <summary>
        /// 根据条件查询商品列表
        /// </summary>
        /// <param name="obj"></param>
        /// <returns>List<EProductInfo></returns>
        public List<EProductInfo> GetProductInfos(EProductInfo obj)
        {
            List<EProductInfo> list = new List<EProductInfo>();
            StringBuilder sql = new StringBuilder();
            sql.Append(@"SELECT  Id ,
                                 ProductName ,
                                 ProductEName ,
                                 CategoryId,
                                 ProductStyle ,
                                 Price ,
                                 ProductDescription ,
                                 IsDelete ,  
                                 IsShelves ,
                                 ImgUrl1 ,
                                 ImgUrl2 ,
                                 ImgUrl3 ,
                                 ImgUrl4 ,
                                 ImgUrl5 ,
                                 OrderId ,
                                 ShoppingCartThumbUrl FROM tb_productinfo WHERE 1=1 AND IsDelete=0 ");
            if (obj != null)
            {
                if (obj.Id > 0)
                {
                    sql.Append(" AND Id=" + obj.Id);
                }

                if (obj.ProductName.Length > 0)
                {
                    sql.Append(" AND ProductName='" + obj.ProductName + "'");
                }
                if (obj.Price > 0)
                {
                    sql.Append(" AND Price=" + obj.Price);
                }
                if (obj.ProductStyle.Length > 0)
                {
                    sql.Append(" AND ProductStyle='" + obj.ProductStyle + "'");
                }
                if (obj.CategoryId > 0)
                {
                    sql.Append(" AND CategoryId=" + obj.CategoryId);
                }
                if (obj.IsShelves != -1)
                {
                    sql.Append(" AND IsShelves=" + obj.IsShelves);
                }
            }
            sql.Append(" ORDER BY OrderId ");
            EProductInfo model = null;
            using (MySqlDataReader sdr = MySqlHelper.ExecuteReader(GZ.DAL.MySQLHelper.connStr, sql.ToString(), null))
            {
                while (sdr.Read())
                {
                    model = new EProductInfo();
                    PrepareModel(model, sdr);
                    list.Add(model);
                }
            }
            return list;
        }
        /// <summary>
        /// 预加载对象
        /// </summary>
        /// <param name="product"></param>
        /// <param name="sdr"></param>
        void PrepareModel(EProductInfo product, MySqlDataReader sdr)
        {
            product.ProductName = sdr["ProductName"].ToString();
            product.Price = Convert.ToDecimal(sdr["Price"]);
            product.CategoryId = Convert.ToInt32(sdr["CategoryId"]);
            product.Id = Convert.ToInt32(sdr["Id"]);
            product.IsShelves = Convert.ToInt32(sdr["IsShelves"]);
            product.ProductDescription = sdr["ProductDescription"].ToString();
            product.ProductEName = sdr["ProductEName"].ToString();
            product.ProductStyle = sdr["ProductStyle"].ToString();
            product.IsShelves = Convert.ToInt32(sdr["IsShelves"]);
            product.ImgUrl1 = sdr["ImgUrl1"].ToString();
            product.ImgUrl2 = sdr["ImgUrl2"].ToString();
            product.ImgUrl3 = sdr["ImgUrl3"].ToString();
            product.ImgUrl4 = sdr["ImgUrl4"].ToString();
            product.ImgUrl5 = sdr["ImgUrl5"].ToString();
            product.ShoppingCartThumbUrl = sdr["ShoppingCartThumbUrl"].ToString();
        }

        /// <summary>
        /// 更新商品信息(图片除外)
        /// </summary>
        /// <param name="obj">商品对象</param>
        /// <returns>True为成功，False为失败</returns>
        public bool Update(EProductInfo obj)
        {
            string sql = @"UPDATE tb_productinfo SET 
                            ProductName = @ProductName,
                            ProductEName = @ProductEName,
                            ProductStyle = @ProductStyle,
                            Price = @Price ,
                            ProductDescription = @ProductDescription, 
                            CategoryId = @CategoryId ,
                            IsShelves = @IsShelves , 
                            OrderId = @OrderId ,
                            ShoppingCartThumbUrl = @ShoppingCartThumbUrl 
                            WHERE Id=@Id";
            MySqlParameter[] parameters = new MySqlParameter[10];
            parameters[0] = new MySqlParameter("@ProductName", obj.ProductName);
            parameters[1] = new MySqlParameter("@ProductEName", obj.ProductEName);
            parameters[2] = new MySqlParameter("@ProductStyle", obj.ProductStyle);
            parameters[3] = new MySqlParameter("@Price", obj.Price);
            parameters[4] = new MySqlParameter("@ProductDescription", obj.ProductDescription);
            parameters[5] = new MySqlParameter("@CategoryId", obj.CategoryId);
            parameters[6] = new MySqlParameter("@IsShelves", obj.IsShelves);
            parameters[7] = new MySqlParameter("@OrderId", obj.OrderId);
            parameters[8] = new MySqlParameter("@ShoppingCartThumbUrl", obj.ShoppingCartThumbUrl);
            parameters[9] = new MySqlParameter("@Id", obj.Id);
            int val = MySqlHelper.ExecuteNonQuery(GZ.DAL.MySQLHelper.connStr, sql, parameters);
            return val > 0 ? true : false;
        }

        /// <summary>
        /// 更新商品图片
        /// </summary>
        /// <returns>True为成功，Fasle为失败</returns>
        public bool UpdateProductImg(EProductInfo obj)
        {
            string sql = @"UPDATE tb_productinfo SET 
                            ImgUrl1 = @ImgUrl1,
                            ImgUrl2 = @ImgUrl2,
                            ImgUrl3 = @ImgUrl3,
                            ImgUrl4 =@ImgUrl4,
                            ImgUrl5 = @ImgUrl5,
                            ShoppingCartThumbUrl=@ShoppingCartThumbUrl
                           WHERE Id=@Id";
            MySqlParameter[] parameters = new MySqlParameter[7];
            parameters[0] = new MySqlParameter("@ImgUrl1", obj.ImgUrl1);
            parameters[1] = new MySqlParameter("@ImgUrl2", obj.ImgUrl2);
            parameters[2] = new MySqlParameter("@ImgUrl3", obj.ImgUrl3);
            parameters[3] = new MySqlParameter("@ImgUrl4", obj.ImgUrl4);
            parameters[4] = new MySqlParameter("@ImgUrl5", obj.ImgUrl5);
            parameters[5] = new MySqlParameter("@ShoppingCartThumbUrl", obj.ShoppingCartThumbUrl);
            parameters[6] = new MySqlParameter("@Id", obj.Id);

            int val = MySqlHelper.ExecuteNonQuery(GZ.DAL.MySQLHelper.connStr, sql, parameters);
            return val > 0 ? true : false;

        }


        /// <summary>
        /// 更新商品口味
        /// </summary>
        /// <param name="obj">商品对象</param>
        /// <returns>True为成功，Fasle为失败</returns>
        public bool UpdateTaste(EProductInfo obj)
        {
            using (MySqlConnection conn = new MySqlConnection(GZ.DAL.MySQLHelper.connStr))
            {
                conn.Open();
                MySqlTransaction trans = conn.BeginTransaction();
                try
                {
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.CommandText = @"DELETE FROM  tb_proudct_taste  WHERE ProductId=@ProductId;";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.AddWithValue("@ProductId", obj.Id);
                    cmd.Transaction = trans;
                    cmd.Connection = conn;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = @"INSERT INTO tb_proudct_taste (ProductId,TasteId,Value) VALUES (@ProductId,@TasteId,@Value)";
                    foreach (Etaste var in obj.Taste)
                    {
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@ProductId", obj.Id);
                        cmd.Parameters.AddWithValue("@TasteId", var.Id);
                        cmd.Parameters.AddWithValue("@Value", var.TasteValue);
                        cmd.ExecuteNonQuery();
                    }
                    trans.Commit();
                    return true;
                }
                catch (MySqlException mysqlex)
                {
                    trans.Rollback();
                    conn.Close();
                    return false;
                }
                finally
                {
                    conn.Close();
                }
            }


        }

        /// <summary>
        /// 根据商品Id获取口味集合
        /// </summary>
        /// <returns>口味集合</returns>
        public IList<Etaste> GetTasteByProductId(int ProductId)
        {
            string sql = @"SELECT T.Id,T.TasteName,T.StartKey,T.EndKey FROM tb_taste AS T LEFT JOIN tb_proudct_taste AS PT ON T.Id=PT.TasteId WHERE PT.ProductId=@ProductId";
            MySqlParameter[] parameters = new MySqlParameter[1];
            parameters[0] = new MySqlParameter("@ProductId", ProductId);
            return GZ.Common.ModelConvertHelper<Etaste>.ConvertToModel(MySqlHelper.ExecuteDataset(GZ.DAL.MySQLHelper.connStr, sql, parameters).Tables[0]);
        }

        #endregion

    }
}
